package packageGenerateData;

import packageInputData.DbConnection;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

import routine.FunctionLib;

public class GenerateDataZTD {
	
	/**
	 * function 
	 * to generate ZTDTemp data and put it into the database
	 * @return			void
	*/
	@SuppressWarnings({ "rawtypes", "deprecation" })
	public static void generateZTDTemp()
	{
		
		
	  	List 	list1 = new ArrayList(),
	  			list2 = new ArrayList(),
	  			list3 = new ArrayList(),
	  			list4= new ArrayList();		
		
		double [] hmf=new double [2];
		double ztd=0;		
		String req = "",siteName="";
		int inserts=0;
		
		//connect to the database
		Connection conn=DbConnection.connectDB();					  			
	  	QueryRunner qRunner = new QueryRunner();
		//get data from table "param"
	  	int paramDoy= 		(Integer)(DbConnection.getFromDB("doy", "param"));
		int paramEpokDeb= 	(Integer)(DbConnection.getFromDB("epok_deb", "param"));
		int paramEpokEnd= 	(Integer)(DbConnection.getFromDB("epok_fin", "param"));
		int paramEpokInc= 	(Integer)(DbConnection.getFromDB("epok_inc", "param"));
			
		//get GPS code list from sites_gps in list1 then in data[]
		try {
		       list1 =(List) qRunner.query(conn,
							    		   "select code from sites_gps",
							    		   new ArrayListHandler());
		} catch (SQLException e2) {
		       e2.printStackTrace();
		}
	
		for (int epok=paramEpokDeb;epok<=paramEpokEnd;epok+=paramEpokInc)
		{
			for(int j=0;j<list1.size();j++)
			{
				Object data[] = (Object[]) list1.get(j);
				siteName=data[0].toString().toUpperCase();
				//prepare query
				req=	"select epok_id from vcopsepok where epok_time= "+
						epok+" and sta_name=? and session_id= "+
						MainClass.session;
				//execute query
				try {
				       list2 =(List) qRunner.query(conn,
									    		   req,
									    		   siteName,
									    		   new ArrayListHandler());
				} catch (SQLException e2) {
				       e2.printStackTrace();
				}
				//get the result
				if(list2.size()>0)
				{
					for(int k=0;k<list2.size();k++)
					{
						Object data2[] = (Object[]) list2.get(k);
						try {
							//prepare query
							req="select lat,alt from sites_all where code=?";
							//execute query
							list3 =(List) qRunner.query(conn,
										    		   req,
										    		   siteName,
										    		   new ArrayListHandler());
							//prepare query
							req=	"select el,sl from epok where id=?  and session_id= "+
									MainClass.session;
							//execute query
							list4 =(List) qRunner.query(conn,
						    		   req,
						    		   Integer.parseInt(data2[0].toString()),
						    		   new ArrayListHandler());
						} catch (SQLException e2) {
						       e2.printStackTrace();
						}
						//get the result of the two query above
						if(list3.size()>0 && list4.size()>0)
						{
						Object data3[] = (Object[]) list3.get(0);
						Object data4[] = (Object[]) list4.get(0);
						
						hmf= FunctionLib.NMFH2Cops(
								paramDoy, 
								Double.parseDouble(data3[0].toString()), 
								Double.parseDouble(data3[1].toString()), 
								Double.parseDouble(data4[0].toString())
								);					
						
						ztd=(Double.parseDouble(data4[1].toString()))/hmf[0];
						//insert into ztd_temp table
						//prepare query
						req=	"INSERT INTO ztd_temp" +
								"(session_id,ztd,epok_time,site)" +				
								"VALUES (?,?,?,?)";
						//execute query
						try {
							inserts = qRunner.update(conn,req,
										MainClass.session,
										ztd,
										epok,
										siteName);
						} catch (SQLException e) {									
							e.printStackTrace();
						}
						
						}
					}
				}
			}
			//test if insertion is done properly
			 if (inserts==1)
				{
	  				System.out.println("Insertion in ztd_temp for epok "+epok +" done ");
	  				inserts=0;
				}
			
		}
		//close connection
		DbUtils.closeQuietly(conn);	
	}
	/**
	 * function 
	 * to generate ZTDHmf data and put it into the database
	 * @return			void
	*/
	@SuppressWarnings({ "rawtypes", "deprecation" })
	public static void generateZTDHmf()
	{
	  	ArrayList <Double> res		 =new ArrayList<Double>();
	  	ArrayList <Integer> epokList =new ArrayList<Integer>();
	  	ArrayList <String> resultList =new ArrayList<String>();
	  	
	 	List list1 = new ArrayList();
		List list2 = new ArrayList();
		
		String siteName="";
		String req="";	
		
		int inserts=0;		
		Double result=0.0;
		
		//Connect to the data base
		Connection conn=DbConnection.connectDB();					  			
	  	QueryRunner qRunner = new QueryRunner();
	  	//get data from table "param"	  	
	  	int paramDoy= 		(Integer)(DbConnection.getFromDB("doy", "param"));
		int paramEpokDeb= 	(Integer)(DbConnection.getFromDB("epok_deb", "param"));
		int paramEpokEnd= 	(Integer)(DbConnection.getFromDB("epok_fin", "param"));
		int paramEpokInc= 	(Integer)(DbConnection.getFromDB("epok_inc", "param"));
			
		//get GPS code list from sites_gps in list1 then in data[]
		try {
		       list1 =(List) qRunner.query(conn,
							    		   "select code from sites_gps",
							    		   new ArrayListHandler());
		} catch (SQLException e2) {
		       e2.printStackTrace();
		}		
		//loop gps sites
		for(int j=0;j<list1.size();j++)
		{
			Object data[] = (Object[]) list1.get(j);
			siteName=data[0].toString().toUpperCase();
			//System.out.println("Station in process: "+siteName);
			
			//loop epok
			for (int epok=paramEpokDeb;epok<=paramEpokEnd;epok+=paramEpokInc)
			{								
				try {
					req=	"select ztd from ztd_temp where epok_time= "+
							epok +" and site=?  and session_id= "+
							MainClass.session;
					
					list2 =(List) qRunner.query(conn,
								    		   req,
								    		   siteName,
								    		   new ArrayListHandler());						
				} catch (SQLException e2) {
					e2.printStackTrace();			
				}
				
				//to calculated mean value
				if(list2.size()>0)
				{					
					for(int k=0;k<list2.size();k++)
					{
						Object data2[] = (Object[]) list2.get(k);
						res.add(Double.parseDouble(data2[0].toString()));
						result+=res.get(k);
					}	
					result/=res.size();
					resultList.add(result.toString());
					epokList.add(epok);
					// values re-initialisation
					result=0.0;
					res.clear();					
				}
				else
				{
					resultList.add("NaN");
					epokList.add(epok);
				} 
				//End epok loop
			}			
			//replace all NaN values if existed
			String [] epoklist1=new String[resultList.size()];
			double []resultList2 = new double [resultList.size()];
			
			for (int n=0;n<resultList.size();n++)							
			{						
				epoklist1[n]=resultList.get(n);											
				
			}
			resultList2=FunctionLib.fillNans(epoklist1);
			
			for (int i=0;i<resultList.size();i++)
			{
				//prepare query
				req="INSERT INTO ztdhmf" +
						"(session_id,doy,epok,ztd,site)" +				
						"VALUES (?,?,?,?,?)";
				//execute query
				 try {
						inserts = qRunner.update(conn,req,
									MainClass.session,
									paramDoy,
									epokList.get(i),
									resultList2[i],
									siteName);
				 } catch (SQLException e) {									
						e.printStackTrace();
				 }
				
			}
			//clear lists
			resultList.clear();
			epokList.clear();			
			//End loop gps sites
			
		}
		
		//check if insertion is done properly
		 if (inserts==1)
			{
 				System.out.println("Insertion in ztdhmf  done ");
 				inserts=0;
			}
		//close connection
		DbUtils.closeQuietly(conn);
	}
}
